package com.neusoft.bizcore.web.utils;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Random;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFSheet;

public class EasyPoiExcelUtil {
    /**
     * HSSF校验
     *
     * @param Sheet sheet
     * @param firstRow 开始行号
     * @param lastRow 结束行号
     * @param firstCol 第几列开始添加下拉框
     * @param lastCol 第几列结束添加下拉框
     * @param strings 下拉框数据
     */
    public static void sheetConstraintHSSF(final Sheet sheet, final int firstRow, final int lastRow,
            final int firstCol,
            final int lastCol,
            final List<String> strings) {
        //  生成下拉列表 只对(x，x)单元格有效
        final CellRangeAddressList cellRangeAddressList =
                new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        //  生成下拉框内容
        final DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(strings.toArray(new String[1]));
        final HSSFDataValidation dataValidation = new HSSFDataValidation(cellRangeAddressList, dvConstraint);
        //  对sheet页生效
        sheet.addValidationData(dataValidation);

    }

    /**
     * XSSF校验
     *
     * @param Sheet sheet
     * @param firstRow 开始行号
     * @param lastRow 结束行号
     * @param firstCol 第几列开始添加下拉框
     * @param lastCol 第几列结束添加下拉框
     * @param strings 下拉框数据
     */
    public static void sheetConstraintXSSF(final Sheet sheet, final int firstRow, final int lastRow,
            final int firstCol, final int lastCol, final List<String> strings) {
        //  生成下拉列表 只对(x，x)单元格有效
        final CellRangeAddressList cellRangeAddressList =
                new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        final XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
        final XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
                .createExplicitListConstraint(strings.toArray(new String[1]));
        final XSSFDataValidation validation =
                (XSSFDataValidation) dvHelper.createValidation(dvConstraint, cellRangeAddressList);
        validation.setShowErrorBox(true);
        sheet.addValidationData(validation);

    }

    /**
     * 为workbook中的每个sheet页加校验
     *
     * @param constraint 校验数据 col 表示添加校验的列号， list为添加校验的数据
     * @param workbook 工作表
     * @param startRow 开始行
     * @param lastRow 结束行
     */

    @SuppressWarnings("unchecked")
    public static void setConstraint(final List<Map<String, Object>> constraint, final Workbook workbook,
            final int startRow,
            final int lastRow) {
        //将下拉框数据放到新的sheet里，然后excel通过新的sheet数据加载下拉框数据
        final Sheet hidden = workbook.createSheet("hidden");
        //创建单元格对象
        Cell cell = null;
        for (final Map<String, Object> map : constraint) {
            final Integer col = (Integer) map.get("col");
            final List<String> everyConstraint = (List<String>) map.get("list");
            if (everyConstraint.size() > 0) {
                //遍历，将数据取出来放到新sheet的单元格中
                for (int i = 0, length = everyConstraint.size(); i < length; i++) {
                    //取出数组中的每个元素
                    final String name = everyConstraint.get(i);
                    //根据i创建相应的行对象
                    final Row row = hidden.getRow(i) == null ? hidden.createRow(i) : hidden.getRow(i);
                    //创建每一行中的第一个单元格
                    cell = row.createCell(col);
                    //然后将元素赋值给这个单元格
                    cell.setCellValue(name);
                }
                // 设置名称引用的公式
                final char a = (char) ('A' + col);
                // 创建名称，可被其他单元格引用
                final Name namedCell = workbook.createName();
                namedCell.setNameName("hidden" + col);
                namedCell.setRefersToFormula(
                        "hidden" + "!$" + String.valueOf(a) + "$1:$" + String.valueOf(a) + "$"
                                + everyConstraint.size());
                final CellRangeAddressList regions = new CellRangeAddressList(startRow,
                        lastRow, col, col);
                //给每个sheet页加相同的校验
                final Iterator<Sheet> iterator = workbook.iterator();
                while (iterator.hasNext()) {
                    final Sheet sheet = iterator.next();
                    if (sheet.getSheetName().equals("hidden")) {
                        break;
                    }
                    final XSSFDataValidationHelper dvHelper =
                            new XSSFDataValidationHelper((XSSFSheet) sheet);
                    //加载数据,将名称为hidden的sheet中的数据转换为List形式
                    final XSSFDataValidationConstraint dvConstraint =
                            (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint("hidden" + col);
                    final XSSFDataValidation validation =
                            (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
                    validation.setShowErrorBox(true);
                    //将数据赋给下拉列表
                    sheet.addValidationData(validation);
                }
            }
        }
        //将hidden sheet设置为隐藏
        workbook.setSheetHidden(workbook.getSheetIndex("hidden"), true);
    }

    /**
     * 设置多级联动 各个参数举个例子说明，以三级联动省，市，县为例 辽宁省：盘锦市（盘山，兴隆台），大连市（中山，甘井子）
     * 江苏省：南京市（江宁，六合），盐城（响水，射阳）
     *
     * @param workbook
     * @param mainParent 所有省级列表 {辽宁，江苏}
     * @param allParent 所有有子节点的 {辽宁，江苏，盘锦，大连，南京，盐城}
     * @param children key 是all
     *        parent中的值，value就是其对应的子集，例：{key:"辽宁",value:[盘锦，大连]}{key:"盘锦",value:[盘山，兴隆台]}
     * @param columns
     *        有序的，第一个值必须是所导出的workbook中的'省'对应的列号，第二个值必须是所导出的workbook中的'市'对应的列号，以此类推
     * @param startRow 开始校验的行号
     * @param endRow 结束校验的行号
     */
    public static void cascade(final Workbook workbook, final List<String> mainParent, final List<String> allParent,
            final Map<String, List<String>> children,
            final ArrayList<Integer> columns, final int startRow, final int endRow) {

        //创建一个专门用来存放地区信息的隐藏sheet页
        //因此也不能在现实页之前创建，否则无法隐藏。
        String hideSheetName = "area";
        while (workbook.getSheet(hideSheetName) != null) {
            hideSheetName = hideSheetName + new Random().nextInt(100);
        }
        final Sheet hideSheet = workbook.createSheet(hideSheetName);
        //这一行作用是将此sheet隐藏，功能未完成时注释此行,可以查看隐藏sheet中信息是否正确
        workbook.setSheetHidden(workbook.getSheetIndex(hideSheet), true);

        int rowId = 0;
        // 设置第一行，存省的信息
        final String parentNameKey = "父列表";
        final Row provinceRow = hideSheet.createRow(rowId++);
        provinceRow.createCell(0).setCellValue(parentNameKey);
        for (int i = 0; i < mainParent.size(); i++) {
            final Cell provinceCell = provinceRow.createCell(i + 1);
            provinceCell.setCellValue(mainParent.get(i));
        }
        // 添加父级别的名称管理器
        final String parentRange = EasyPoiExcelUtil.getRange(1, rowId, mainParent.size());
        final Name parentName = workbook.createName();
        //key不可重复
        parentName.setNameName(parentNameKey);
        final String parentFormula = hideSheetName + "!" + parentRange;
        parentName.setRefersToFormula(parentFormula);

        // 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
        for (final String key : allParent) {
            final String[] son = children.get(key).toArray(new String[1]);
            final Row row = hideSheet.createRow(rowId++);
            row.createCell(0).setCellValue(key);
            for (int j = 0; j < son.length; j++) {
                final Cell cell = row.createCell(j + 1);
                cell.setCellValue(son[j]);
            }

            // 添加名称管理器
            final String range = EasyPoiExcelUtil.getRange(1, rowId, son.length);
            final Name name = workbook.createName();
            //key不可重复
            name.setNameName(key);
            final String formula = hideSheetName + "!" + range;
            System.out.println(formula);
            name.setRefersToFormula(formula);
        }

        //给每个sheet页加相同的校验
        final Iterator<Sheet> iterator = workbook.iterator();
        while (iterator.hasNext()) {
            final Sheet sheetPro = iterator.next();
            if (sheetPro.getSheetName().equals(hideSheetName)) {
                break;
            }

            final XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) sheetPro);

            //最外层的规则 加载数据,将名称为hidden的sheet中的数据转换为List形式
            final XSSFDataValidationConstraint dvConstraint =
                    (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(parentNameKey);
            // 四个参数分别是：起始行、终止行、起始列、终止列
            final CellRangeAddressList provRangeAddressList =
                    new CellRangeAddressList(startRow, endRow, columns.get(0), columns.get(0));
            final XSSFDataValidation provinceDataValidation =
                    (XSSFDataValidation) dvHelper.createValidation(dvConstraint, provRangeAddressList);

            //验证
            provinceDataValidation.createErrorBox("error", "请选择正确的数据");
            provinceDataValidation.setShowErrorBox(true);
            provinceDataValidation.setSuppressDropDownArrow(true);
            sheetPro.addValidationData(provinceDataValidation);

            for (int j = 0; j < (columns.size() - 1); j++) {
                final String column = String.valueOf(((char) (('A' + columns.get(j)))));
                EasyPoiExcelUtil.setDataValidation(column, (XSSFSheet) sheetPro, startRow, endRow, columns.get(j + 1));
            }
        }
    }

    /**
     * 设置有效性
     *
     * @param offset 主影响单元格所在列，即此单元格由哪个单元格影响联动
     * @param sheet
     * @param rowNum 行数
     * @param colNum 列数
     */
    public static void setDataValidation(final String offset, final XSSFSheet sheet, final int startRow,
            final int endRow,
            final int colNum) {
        final XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
        DataValidation data_validation_list;
        data_validation_list = EasyPoiExcelUtil.getDataValidationByFormula(
                "INDIRECT($" + offset + (2) + ")", startRow, endRow, colNum, dvHelper);
        sheet.addValidationData(data_validation_list);
    }

    /**
     * 加载下拉列表内容
     *
     * @param formulaString
     * @param naturalRowIndex
     * @param naturalColumnIndex
     * @param dvHelper
     * @return
     */
    private static DataValidation getDataValidationByFormula(
            final String formulaString, final int firstRow, final int lastRow, final int naturalColumnIndex,
            final XSSFDataValidationHelper dvHelper) {
        // 加载下拉列表内容
        // 举例：若formulaString = "INDIRECT($A$2)" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据，
        //如果A2是江苏省，那么此处就是江苏省下的市信息。
        final XSSFDataValidationConstraint dvConstraint =
                (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(formulaString);
        // 设置数据有效性加载在哪个单元格上。
        // 四个参数分别是：起始行、终止行、起始列、终止列
        final CellRangeAddressList regions = new CellRangeAddressList(firstRow,
                lastRow, naturalColumnIndex, naturalColumnIndex);
        // 数据有效性对象
        // 绑定
        final XSSFDataValidation data_validation_list =
                (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
        data_validation_list.setEmptyCellAllowed(false);
        if (data_validation_list instanceof XSSFDataValidation) {
            data_validation_list.setSuppressDropDownArrow(true);
            data_validation_list.setShowErrorBox(true);
        } else {
            data_validation_list.setSuppressDropDownArrow(false);
        }
        // 设置输入信息提示信息
        data_validation_list.createPromptBox("下拉选择提示", "请使用下拉方式选择合适的值！");
        return data_validation_list;
    }

    /**
     * 计算formula
     *
     * @param offset 偏移量，如果给0，表示从A列开始，1，就是从B列
     * @param rowId 第几行
     * @param colCount 一共多少列
     * @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
     */
    public static String getRange(final int offset, final int rowId, final int colCount) {
        final char start = (char) ('A' + offset);
        if (colCount <= 25) {
            final char end = (char) ((start + colCount) - 1);
            return "$" + start + "$" + rowId + ":$" + end + "$" + rowId;
        } else {
            char endPrefix = 'A';
            char endSuffix = 'A';
            if ((((colCount - 25) / 26) == 0) || (colCount == 51)) {// 26-51之间，包括边界（仅两次字母表计算）
                if (((colCount - 25) % 26) == 0) {// 边界值
                    endSuffix = (char) ('A' + 25);
                } else {
                    endSuffix = (char) (('A' + ((colCount - 25) % 26)) - 1);
                }
            } else {// 51以上
                if (((colCount - 25) % 26) == 0) {
                    endSuffix = (char) ('A' + 25);
                    endPrefix = (char) ((endPrefix + ((colCount - 25) / 26)) - 1);
                } else {
                    endSuffix = (char) (('A' + ((colCount - 25) % 26)) - 1);
                    endPrefix = (char) (endPrefix + ((colCount - 25) / 26));
                }
            }
            return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId;
        }
    }
}
